﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using RED_NET_Core.Helper;
using RED_NET_Core.Entity;
using System.IO;
using System.Configuration;
using System.Collections;
using System.Web;

namespace RED_NET_Core.DAL
{
    public class Class_DAL
    {
        public Class_DAL() { }//构造函数
        #region[将excel表导成DataSet]
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileNamePath"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        /// 使用OleDbDataAdapter直接读取excel文件，放入到DataTable中
        public DataSet TransformFromExcelToDataSet(string filePath, string table)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source = " + filePath +
                ";Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection cn = new OleDbConnection(strConn);//链接表
            cn.Open();//打开表
            OleDbDataAdapter OleAdp = new OleDbDataAdapter("select * from [Sheet1$]", cn);
            DataSet da = new DataSet();
            OleAdp.Fill(da, table);
            return da;
        }
        #endregion


        #region[利用dataset批量导入班级成员信息]
        public int InsetIntoUser(DataSet da,int tBeOfUserId)
        {
            int sum = 0;//计算已存在的用户并返回。
            SqlHelper helper = new SqlHelper();
            DataRow[] dr = da.Tables[0].Select();//查找表的行
            string tClass, tCollege, tMajor, tSex, tLongTel, tShortTel, tUid, tName;
            for (int i = 0; i < dr.Length; i++)
            {
                tUid = dr[i]["学号"].ToString();
                //若已存在该用户则不继续操作
                if (IsUIdExist(tUid) != 0)
                {
                    sum++;
                    continue;
                }
                tClass = dr[i]["班级"].ToString();
                tMajor = dr[i]["专业"].ToString();
                tCollege = dr[i]["学院"].ToString();
                tSex = dr[i]["性别"].ToString();
                tName = dr[i]["姓名"].ToString();
                tLongTel = dr[i]["长号"].ToString();
                tShortTel = dr[i]["短号"].ToString();
                try
                {
                    Registe(tUid, tClass, tMajor, tCollege, tBeOfUserId, tSex, tName, tLongTel, tShortTel);//可能出错的地方，调入try中
                    //如果Registe函数有错误（包括数据类型错误），抛出后就会在这里被接受，但这里还不是处理的地方，所以继续抛出
                }
                catch (Exception ex)
                {
                    throw ex;//抛出异常
                }
            }

            return sum;
        }

        #endregion

        #region[检测此班级成员属否存在]
        public int IsUIdExist(string UId)
        {
            SqlHelper sqlhelper = new SqlHelper();
            string cmd = "select * from _Student where _Uid = @Uid";
            SqlParameter[] pram = new SqlParameter[]
            {
                new SqlParameter("@UId",UId)
            };
            try
            {
                SqlCommand sqlcmd = sqlhelper.GetCommand(cmd, pram);
                SqlDataReader sdr = sqlcmd.ExecuteReader();
                if (sdr.Read())
                    return 1;
                else return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[]

        public bool Registe(string tUId,String tClass, String tMajor, String tCollege, int BeOfUserId, string tSex, string tName,  string tLongTel, string tShortTel)
        {
            SqlHelper helper = new SqlHelper();
            string CmdText = "INSERT INTO _Student(_UId,_Class,_Major,_College,_BeOfUserId,_Sex,_Name,_LongTel," +
                "_ShortTel) VALUES(@UId,@Class,@Major,@College,@BeOfUserId,@Sex,@Name,@LongTel,@ShortTel)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Uid",tUId),
                new SqlParameter("@Class",tClass),
                new SqlParameter("@Major",tMajor),
                new SqlParameter("@College",tCollege),
                new SqlParameter("@BeOfUserId",BeOfUserId),
                new SqlParameter("@sex",tSex),
                new SqlParameter("@Name",tName),              
                new SqlParameter("@LongTel",tLongTel),
                new SqlParameter ("@ShortTel",tShortTel),
                
            };
            bool IsOK;
            SqlCommand Cmd = helper.GetCommand(CmdText, param);
            try
            {
                Cmd.ExecuteNonQuery();//因为可能出错 所以放在try里面
                IsOK = true;
            }
            catch (Exception ex)
            {
                IsOK = false;
                throw ex;//捕捉到异常，抛出
            }
            return IsOK;
        }

        #endregion

        #region[getinfo方法]
        public DataTable GetInfo(int UserId)
        {
            SqlHelper helper = new SqlHelper();
            string CommandText = "select _Id as 主键号, _Uid as 学号,_Class as 班级,_Major as 专业,_College as 学院,_Sex as 性别,_Name as 姓名,_LongTel as 长号,_ShortTel as 短号,_VolunteerTime as 志愿者时间 from _Student WHERE _BeOfUserId = @UserId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@UserId", UserId)
            };
            SqlCommand mycommand = helper.GetCommand(CommandText, parm);
            DataTable dt = new DataTable();
            using (SqlDataReader mysqldatareader = mycommand.ExecuteReader())
            {
                dt.Load(mysqldatareader);
            }

            return dt;

        }
        #endregion
     

        #region[新建基本信息]
        public bool Insert(string tUid, String tName, string tSex,string tCollege,string tMajor,string tClass, string tPosition,
            string tPoliticsStatus, string tLongTel, string tShortTel,string tBeOfUserId)
        //string _Achievements,string _VolunteerTime,string _ReceivedHonors,
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Student(_Uid,_Name,_Sex,_College,_Major,_Class,_Position,_PoliticsStatus,_LongTel,_ShortTel,_BeOfUserId) VALUES(@Uid,@Name,@Sex,@College,@Major,@Class,@Position,@PoliticsStatus,@LongTel,@ShortTel,@BeOfUserId)";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@Uid",tUid),
                new SqlParameter("@Name",tName),
                new SqlParameter("@Sex",tSex),
                new SqlParameter("@College",tCollege),
                new SqlParameter("@Major",tMajor),
                new SqlParameter("@Class",tClass),
                new SqlParameter("@Position",tPosition),
                new SqlParameter("@PoliticsStatus",tPoliticsStatus),
                new SqlParameter("@LongTel",tLongTel),
                new SqlParameter("@ShortTel",tShortTel),
                new SqlParameter ("@BeOfUserId",tBeOfUserId),
                
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找基本信息]
        public DataTable Select(int _Id)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Select _Id,_Uid,_Name,_Sex,_Position,_PoliticsStatus,_LongTel,_ShortTel,_BeOfUserId from _Student where _Class = @Id";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Id",_Id)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            DataTable dt = new DataTable();
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[更新基本信息]
        public bool Update(string tId, string tPoliticsStatus, string tLongTel, string tShortTel)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Student SET _PoliticsStatus=@PoliticsStatus,_LongTel=@LongTel,_ShortTel=@ShortTel WHERE _Id=@Id";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@Id",tId),
                new SqlParameter("@PoliticsStatus",tPoliticsStatus),
                new SqlParameter("@LongTel",tLongTel),
                new SqlParameter("@ShortTel",tShortTel),
                
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);

            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[删除基本信息]
        public bool Delete(int _Id)
        {
            string strSQL = "delete _Student where  _Id=" + _Id.ToString();
            try
            {
                SqlHelper.ExecuteSql(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
        
   
        #region[新建学术科研成果]
        public bool InsertResearch(string tContent,string tBeOfStdId)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Research(_Content,_BeOfStdId) VALUES(@Content,@BeOfStdId)";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@Content",tContent),
                new SqlParameter("@BeOfStdId",tBeOfStdId),
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找学术科研成果]
        public DataTable SelectResearch(int _ResearchId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Select _ResearchId,_Content from _Research where _BeOfStdId = @ResearchId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@ResearchId",_ResearchId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            DataTable dt = new DataTable();
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[更新学术科研成果]
        public bool UpdateResearch(string tResearchId, string tContent)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Research SET _Content=@Content WHERE _ResearchId=@ResearchId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@ResearchId",tResearchId),
                new SqlParameter("@Content",tContent),               
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[删除学术科研成果]
        public bool DeleteResearch(int _ResearchId)
        {
            string strSQL = "delete _Research where  _ResearchId=" + _ResearchId.ToString();
            try
            {
                SqlHelper.ExecuteSql(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


        #region[新建志愿者活动]
        public bool InsertVt( string tContent,string tAddTime, string tBeOfStdId)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Volunteer(_Content,_AddTime,_BeOfStdId) VALUES(@Content,@AddTime,@BeOfStdId)";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@Content",tContent),
                new SqlParameter("@AddTime",tAddTime),
                new SqlParameter("@BeOfStdId",tBeOfStdId),
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[查找志愿者活动]
        public DataTable SelectVt(int _VolunteerId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Select _VolunteerId,_Content,_AddTime from _Volunteer where _BeOfStdId = @VolunteerId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@VolunteerId",_VolunteerId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            DataTable dt = new DataTable();
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[更新志愿者活动]
        public bool UpdateVt(string tVolunteerId, string tContent, string tAddTime)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Volunteer SET _Content=@Content,_AddTime=@AddTime WHERE _VolunteerId=@VolunteerId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@VolunteerId",tVolunteerId),
                new SqlParameter("@Content",tContent), 
                new SqlParameter("@AddTime",tAddTime),
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[删除志愿者活动]
        public bool DeleteVt(int _VolunteerId)
        {
            string strSQL = "delete _Volunteer where  _VolunteerId=" + _VolunteerId.ToString();
            try
            {
                SqlHelper.ExecuteSql(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


        #region[新建竞赛活动]
        public bool InsertContest(string tContent, string tBeOfStdId)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Contest(_Content,_BeOfStdId) VALUES(@Content,@BeOfStdId)";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@Content",tContent),
                new SqlParameter("@BeOfStdId",tBeOfStdId),
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[查找竞赛活动]
        public DataTable SelectContest(int _ContestId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Select * from _Contest where _BeOfStdId = @_ContestId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@_ContestId",_ContestId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            DataTable dt = new DataTable();
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[更新竞赛活动]
        public bool UpdateContest(string tContestId, string tContent)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Contest SET _Content=@Content WHERE _ContestId=@ContestId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@ContestId",tContestId),
                new SqlParameter("@Content",tContent),               
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[删除竞赛活动]
        public bool DeleteContest(int _ContestId)
        {
            string strSQL = "delete _Contest where  _ContestId=" + _ContestId.ToString();
            try
            {
                SqlHelper.ExecuteSql(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


        #region[新建所获荣誉]
        public bool InsertHonour(string tContent, string tBeOfStdId)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Honour(_Content,_BeOfStdId) VALUES(@Content,@BeOfStdId)";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@Content",tContent),
                new SqlParameter("@BeOfStdId",tBeOfStdId),
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[查找所获荣誉]
        public DataTable SelectHonour(int _HonourId)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Select _HonourId,_Content from _Honour where _BeOfStdId = @HonourId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@HonourId",_HonourId)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            DataTable dt = new DataTable();
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[更新所获荣誉]
        public bool UpdateHonour(string tHonourId, string tContent)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Honour SET _Content=@Content WHERE _HonourId=@HonourId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@HonourId",tHonourId),
                new SqlParameter("@Content",tContent),               
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region[删除所获荣誉]
        public bool DeleteHonour(int _HonourId)
        {
            string strSQL = "delete _Honour where  _HonourId=" + _HonourId.ToString();
            try
            {
                SqlHelper.ExecuteSql(strSQL);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        //学生信息
        #region[查找单条学生信息]

        public DataTable GetStuInfo(int iStu)
        {
            SqlHelper helper = new SqlHelper();
            string cmdText = "Select * From _Student Where _Id = @StuId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@StuId",iStu)
            };
            SqlCommand cmd = helper.GetCommand(cmdText, param);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(sdr);
                sdr.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}

